package com.zhucai.credit.repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.zhucai.credit.model.CreditRecordEntrity;

/**
 * 融资记录Repository
 */
@Repository
public interface CreditRecordRepository extends
		JpaRepository<CreditRecordEntrity, Long> {

	/**
	 * 根据条件查询融资记录
	 * @author hsg
	 * @version 2018年7月16日
	 * @param delete
	 * @param originator
	 * @param participator
	 * @param statusList
	 * @param searchParm
	 * @param purUserList
	 * @param supUserList
	 * @param purDepartmentList
	 * @param supDepartmentList
	 * @param unusualList 
	 * @param waitPayOrderStatus 
	 * @param projectId
	 * @param bankId 
	 * @param pageable
	 * @return
	 */
	@Query(value = "select  fcr.contract_num,fcr.payment_num,fcr.record_no,fpt.cr_prod_bank_name,"
			+ " fecps.enterprise_name pur_enterprise_name,fecp.enterprise_name sup_enterprise_name,"
			+ " fcr.credit_amount,fcr.credit_get_amount,fcr.loan_date,fcr.project_tid,fcr.credit_status,fcr.id,"
			+ " fcr.pur_repayment_amount,fcr.sup_repayment_amount,fcr.payment_id,fcr.draw_amount,fcr.repayment_amount,fcr.draw_rate,fcr.reject_reason,"
			+ " fcr.loan_date_ccb,fcr.invoice_tid,txpp.project_name,fcr.credit_unusual_status,fcr.participator,fcr.cr_prod_id,"
			+ " IF (fpoi.pay_status IS NULL,'01',fpoi.pay_status) pay_status,"
			+ " fecp1.cr_prod_rate sup_cr_prod_rate,fecp2.cr_prod_rate pur_cr_prod_rate  from finance_cr_record fcr "
			+ " left join finance_ent_account fecps on fecps.id=fcr.originator"
			+ " left join finance_ent_account fecp on fecp.id=fcr.participator"
			+ " left join finance_product_type fpt on fpt.id=fcr.cr_prod_id "
			+ " left join tb_xa_payment_plan pp on fcr.payment_id = pp.id"
			+ " left join tb_xa_invoice_info txii on fcr.invoice_tid = txii.id"
			+ " left join tb_xa_user_department txup1 on txup1.user_id = fcr.originator_user_id"
			+ " left join tb_xa_user_department txup2 on txup2.user_id = fcr.participator_user_id"
			+ " left join tb_xa_purchaserproject txpp on txpp.id = fcr.project_tid"
			+ " left join finance_ent_cr_prod fecp1 on fecp1.en_acc_id = fcr.participator and  fecp1.cr_pr_type_id = fcr.cr_prod_id and fecp1.zhucai_prod_type = 'ZHU_CAI_B_TONG'"
			+ " left join finance_ent_cr_prod fecp2 on fecp2.en_acc_id = fcr.originator and  fecp2.cr_pr_type_id = fcr.cr_prod_id and fecp2.zhucai_prod_type = 'ZHU_CAI_B_TONG'"
			+ " left join finance_pay_order_item fpoi on fpoi.order_no = fcr.record_no and fpoi.pay_status  in ('02','05') and fpoi.trade_status = 'NORMAL'"
			+ " where pp.status <> 3 and fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ "	and fcr.credit_status in (?4)"
			+ " and (fcr.originator_user_id in (?5) or"
			+ " fcr.participator_user_id in (?6))"
			+ " and ((txup1.department_id is not null and txup1.department_id in(?7)) or (txup2.department_id is not null and txup2.department_id in (?8)))"
			+ " and (?9 is null or fcr.project_tid = ?9)"
			+ " and (?10 is null or fcr.cr_prod_id =?10) "
			+ " and (?11 is null or fcr.record_no like concat('%',?11,'%'))"
			+ " and (?12 is null or fcr.contract_num like concat('%',?12,'%'))"
			+ " and (?13 is null or fcr.payment_num like concat('%',?13,'%'))"
			+ " and (?14 is null or fecp.enterprise_name like concat('%',?14,'%'))"
			+ " and (?15 is null or fecps.enterprise_name like concat('%',?15,'%'))"
			+ " and fcr.credit_unusual_status in (?16)"
			+ " and (?17 is null or fpoi.pay_status = ?17)"
			+ " and (?18 is null or (fpoi.pay_status <> ?18 or fpoi.id is null) )"
			+ "	group by fcr.id"
			+ " order by ?#{#pageable}", nativeQuery = true)
	List<Object[]> findCreditRecordList(int delete, Long originator,
			Long participator, List<String> statusList, List<Long> purUserList,
			List<Long> supUserList, List<Long> purDepartmentList,
			List<Long> supDepartmentList, Long projectTid, Long crProdId,
			String recordNo, String contractNum, String paymentNum,
			String supEnteriseName, String purEnteriseName, List<String> unusualList,String orderStatus, String waitPayOrderStatus, Pageable pageable);


	/**
	 * 根据条件查询融资记录条数
	 * @author hsg
	 * @version 2018年7月16日
	 * @param delete
	 * @param originator
	 * @param participator
	 * @param statusList
	 * @param searchParm
	 * @param purUserList
	 * @param supUserList
	 * @param purDepartmentList
	 * @param supDepartmentList
	 * @param projectTid
	 * @param unusualList 
	 * @param waitPayOrderStatus 
	 * @param bankId 
	 * @return
	 */
	@Query(value = "select  count(1) from ("
			+ " select fcr.id "
			+ " from finance_cr_record fcr "
			+ " left join finance_ent_account fecps on fecps.id=fcr.originator"
			+ " left join finance_ent_account fecp on fecp.id=fcr.participator"
			+ " left join finance_product_type fpt on fpt.id=fcr.cr_prod_id "
			+ " left join tb_xa_payment_plan pp on fcr.payment_id = pp.id"
			+ " left join tb_xa_user_department txup1 on txup1.user_id = fcr.originator_user_id"
			+ " left join tb_xa_user_department txup2 on txup2.user_id = fcr.participator_user_id"
			+ " left join finance_pay_order_item fpoi on fpoi.order_no = fcr.record_no and fpoi.pay_status  in ('02','05') and fpoi.trade_status = 'NORMAL'"
			+ " where pp.status <> 3 and fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ "	and fcr.credit_status in (?4)"
			+ " and (fcr.originator_user_id in (?5) or"
			+ " fcr.participator_user_id in (?6))"
			+ " and ((txup1.department_id is not null and txup1.department_id in(?7)) or (txup2.department_id is not null and txup2.department_id in (?8)))"
			+ " and (?9 is null or fcr.project_tid = ?9) "
			+ " and (?10 is null or fcr.cr_prod_id =?10) "
			+ " and (?11 is null or fcr.record_no like concat('%',?11,'%'))"
			+ " and (?12 is null or fcr.contract_num like concat('%',?12,'%'))"
			+ " and (?13 is null or fcr.payment_num like concat('%',?13,'%'))"
			+ " and (?14 is null or fecp.enterprise_name like concat('%',?14,'%'))"
			+ " and (?15 is null or fecps.enterprise_name like concat('%',?15,'%'))"
			+ " and fcr.credit_unusual_status in (?16)"
			+ " and (?17 is null or fpoi.pay_status = ?17)"
			+ " and (?18 is null or (fpoi.pay_status <> ?18 or fpoi.id is null))"
			+ " group by fcr.id"
			+ " ) f"
			, nativeQuery = true)
	Integer findCreditRecordCount(int delete, Long originator,
			Long participator, List<String> statusList, List<Long> purUserList,
			List<Long> supUserList, List<Long> purDepartmentList,
			List<Long> supDepartmentList, Long projectTid, Long crProdId,
			String recordNo, String contractNum, String paymentNum,
			String supEnteriseName, String purEnteriseName, List<String> unusualList,String orderStatus, String waitPayOrderStatus);

	/**
	 * 待支付的融资列表
	 * @version 2018年7月23日
	 * @param delete
	 * @param originator
	 * @param participator
	 * @param statusList
	 * @param purUserList
	 * @param supUserList
	 * @param purDepartmentList
	 * @param supDepartmentList
	 * @param projectTid
	 * @param crProdId
	 * @param recordNo
	 * @param contractNum
	 * @param paymentNum
	 * @param supEnteriseName
	 * @param purEnteriseName
	 * @param unusualList
	 * @return
	 */
	@Query(value = "select fcr.credit_get_amount,fcr.cr_prod_id,fcr.participator,fcr.loan_date,"
			+ "	fecp1.cr_prod_rate sup_cr_prod_rate,fecp2.cr_prod_rate pur_cr_prod_rate"
			+ " from finance_cr_record fcr "
			+ " left join finance_ent_account fecps on fecps.id=fcr.originator"
			+ " left join finance_ent_account fecp on fecp.id=fcr.participator"
			+ " left join finance_product_type fpt on fpt.id=fcr.cr_prod_id "
			+ " left join tb_xa_payment_plan pp on fcr.payment_id = pp.id"
			+ " left join tb_xa_invoice_info txii on fcr.invoice_tid = txii.id"
			+ " left join tb_xa_user_department txup1 on txup1.user_id = fcr.originator_user_id"
			+ " left join tb_xa_user_department txup2 on txup2.user_id = fcr.participator_user_id"
			+ " left join tb_xa_purchaserproject txpp on txpp.id = fcr.project_tid"
			+ " left join finance_ent_cr_prod fecp1 on fecp1.en_acc_id = fcr.participator and  fecp1.cr_pr_type_id = fcr.cr_prod_id and fecp1.zhucai_prod_type = 'ZHU_CAI_B_TONG'"
			+ " left join finance_ent_cr_prod fecp2 on fecp2.en_acc_id = fcr.originator and  fecp2.cr_pr_type_id = fcr.cr_prod_id and fecp2.zhucai_prod_type = 'ZHU_CAI_B_TONG'"
			+ " left join finance_pay_order_item fpoi on fpoi.order_no = fcr.record_no and (fpoi.pay_status ='02' or fpoi.pay_status ='05') and fpoi.trade_status = 'NORMAL'"
			+ " where pp.status <> 3 and fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ "	and fcr.credit_status in (?4)"
			+ " and (fcr.originator_user_id in (?5) or"
			+ " fcr.participator_user_id in (?6))"
			+ " and (txup1.department_id in(?7) or txup2.department_id in (?8))"
			+ " and (?9 is null or fcr.project_tid = ?9)"
			+ " and (?10 is null or fcr.cr_prod_id =?10) "
			+ " and (?11 is null or fcr.record_no like concat('%',?11,'%'))"
			+ " and (?12 is null or fcr.contract_num like concat('%',?12,'%'))"
			+ " and (?13 is null or fcr.payment_num like concat('%',?13,'%'))"
			+ " and (?14 is null or fecp.enterprise_name like concat('%',?14,'%'))"
			+ " and (?15 is null or fecps.enterprise_name like concat('%',?15,'%'))"
			+ " and fcr.credit_unusual_status in (?16)"
			+ " and fpoi.id is null"
			+ "	group by fcr.id"
			, nativeQuery = true)
	List<Object[]> findWaitPayAllAmtList(int delete, Long originator,
			Long participator, List<String> statusList, List<Long> purUserList,
			List<Long> supUserList, List<Long> purDepartmentList,
			List<Long> supDepartmentList, Long projectTid, Long crProdId,
			String recordNo, String contractNum, String paymentNum,
			String supEnteriseName, String purEnteriseName,
			List<String> unusualList);
	


	/**
	 * 查询合作的商家
	 * 
	 * @param delete
	 * @param originator
	 * @param participator
	 * @param bankApproval
	 * @param bankId
	 * @return
	 */
	@Query(value = "select count(1) from ( select count(1)"
			+ " from finance_credit_application fcr where fcr.status<>?1"
			+ " and (?2 is null or fcr.cr_applicant_pur_id=?2)"
			+ " and (?3 is null or fcr.cr_applicant_id=?3 )"
			+ " and (?4 is null or fcr.cr_aproval_status=?4)"
			+ " and (?5 is null or fcr.cr_prod_id=?5)"
			+ " group by if ( ?3 is null , app_orgn_num, coo_orgn_num  )"
			+ ") t", nativeQuery = true)
	Integer findBusCoopCount(int delete, Long originator, Long participator,
			String bankApproval, Long bankId);

	/**
	 * 查询待审核的合作的商家
	 * 
	 * @param delete
	 * @param originator
	 * @param participator
	 * @param bankApproval
	 * @return
	 */
	@Query(value = "select count(1) from finance_credit_application fcr"
			+ " where fcr.status<>?1"
			+ " and (?2 is null or fcr.cr_applicant_pur_id=?2)"
			+ " and (?3 is null or fcr.cr_applicant_id=?3 )"
			+ " and fcr.cr_aproval_status in (?4)"
			+ " and (?5 is null or fcr.cr_prod_id=?5)", nativeQuery = true)
	Integer findAppBusCoopCount(int delete, Long originator, Long participator,
			List<String> bankApprovalList, Long bankId);

	/**
	 * 根据合同编号查询融资记录
	 * 
	 * @date 2017年11月29日 @user hsg
	 */
	CreditRecordEntrity findByStatusNotAndContractId(int delete,
			String contract_num);

	/**
	 * 根据单号数组查询融资记录
	 * 
	 * @date 2017年11月29日 @user hsg
	 */
	@Query(value = "select * from finance_cr_record fcr "
			+ " where fcr.status<>?1" + " and fcr.invoice_no_code in (?2)"
			+ " order by fcr.loan_date asc", nativeQuery = true)
	List<CreditRecordEntrity> findByBillArr(int delete, List<String> bill_list);

	/**
	 * 根据合同编号查询融资记录
	 * 
	 * @date 2017年12月5日 @user hsg
	 */
	CreditRecordEntrity findByStatusNotAndContractNumAndCreditStatusNot(
			int delete, String contract_num, String creditStatus);

	/**
	 * 根据融资状态获取融资记录条数
	 * 
	 * @param long2
	 * @param long1
	 * @date 2017年12月6日 @user hsg
	 */
	@Query(value = "select count(1) from finance_cr_record fcr"
			+ " where fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ " and (?4 is null or fcr.participator_enterprise_id=?4)"
			+ " and (?5 is null or fcr.originator_enterprise_id=?5)"
			+ " and (?6 is null or fcr.credit_status=?6)", nativeQuery = true)
	Integer findCountByStatus(int delete, Long originator, Long participator,
			Long supEntId, Long purEntId, String creditStatus);

	CreditRecordEntrity findByTidAndStatusNot(Long tid, int delete);

	/**
	 * 不按权限查询融资余额（剩余还款金额）
	 * 
	 * @param supId
	 * @param bankId
	 * @date 2017年12月15日 @user hsg
	 */
	@Query(value = "select sum(fcr.draw_amount-ifnull(fcr.repayment_amount,0)) over_amount from"
			+ "	finance_cr_record fcr"
			+ " where fcr.status<>?1"
			+ " and fcr.draw_amount>0 "
			+ " and ifnull(fcr.repayment_amount,0)<fcr.draw_amount"
			+ " and ( ?2 is null or fcr.originator=?2)"
			+ " and ( ?3 is null or fcr.participator=?3)"
			+ " and ( ?4 is null or fcr.cr_prod_id=?4)"
			+ " and fcr.credit_status in (?5)", nativeQuery = true)
	BigDecimal findBalanceAmt(int delete, Long purId, Long supId, Long bankId,
			List<String> statusList);


	/**
	 * 查询累计融资金额（不加权限）
	 * 
	 * @param supUserId
	 * @date 2017年12月15日 @user hsg
	 */
	@Query(value = "select sum(fcr.draw_amount) draw_amount"
			+ " from finance_cr_record fcr" + " where fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ " and fcr.draw_amount>0" + " and fcr.credit_status in (?4)"
			+ " and (?5 is null or fcr.cr_prod_id =?5)", nativeQuery = true)
	BigDecimal findTotalAmount(int delete, Long purId, Long supId,
			List<String> statusList, Long pordId);

	/**
	 * 查询融资信息根据计划付款id或tid
	 * 
	 * @date 2017年12月20日 @user hsg
	 */
	@Query(value = "select fcr.id,fcr.record_no,fcr.originator_user_id,fcr.originator,"
			+ " fcr.participator,fcr.participator_user_id,fcr.cr_prod_id,fcr.payment_num,fcr.payment_id,"
			+ " fcr.contract_id,fcr.contract_num,fcr.contract_num_ccb,fcr.invoice_no_code,fcr.credit_status,"
			+ " fcr.credit_unusual_status,fcr.reject_reason,fcr.credit_amount,fcr.credit_get_amount,"
			+ " fcr.loan_date,fcr.loan_date_ccb,fcr.repayment_amount,fcr.pur_repayment_amount,fcr.sup_repayment_amount,"
			+ " fcr.draw_amount,fcr.draw_rate,fcr.transaction_time,fcr.invoice_tid,fcr.project_tid,fcr.app_num,fea.enterprise_name pur_enterprise_name,"
			+ " feas.enterprise_name sup_enterprise_name,txpp.project_name,txii.invoice_file_name, txii.invoice_no,txii.invoice_code,txii.invoice_amount,"
			+ " txii.invoice_date,fpt.cr_prod_bank_name,txii.invoice_file_id,fecp1.cr_prod_rate sup_cr_prod_rate,fecp2.cr_prod_rate pur_cr_prod_rate from finance_cr_record fcr"
			+ " left join finance_product_type fpt on fcr.cr_prod_id = fpt.id"
			+ "	left join tb_xa_invoice_info txii on fcr.invoice_tid = txii.id"
			+ "	left join finance_ent_account fea on fcr.originator = fea.id"
			+ "	left join finance_ent_account feas on fcr.participator = feas.id"
			+ "	left join tb_xa_purchaserproject txpp on fcr.project_tid = txpp.id"
			+ " left join finance_ent_cr_prod fecp1 on fecp1.en_acc_id = fcr.participator and  fecp1.cr_pr_type_id = fcr.cr_prod_id and fecp1.zhucai_prod_type = 'ZHU_CAI_B_TONG'"
			+ " left join finance_ent_cr_prod fecp2 on fecp2.en_acc_id = fcr.originator and  fecp2.cr_pr_type_id = fcr.cr_prod_id and fecp2.zhucai_prod_type = 'ZHU_CAI_B_TONG'"
			+ " where fcr.status <> ?1  "
			+ " and ( ?2 is null or fcr.payment_id = ?2)"
			+ " and ( ?3 is null or fcr.id = ?3)", nativeQuery = true)
	List<Object[]> findByTidAndPaymentId(int delete, Long payId, Long tid);

	/**
	 * 根据状态查询融资记录
	 * 
	 * @param string
	 * @param delete
	 * @return
	 */
	@Query(value = "select * from finance_cr_record fcr"
			+ " left join finance_product_type fpt on fcr.cr_prod_id = fpt.id"
			+ " where fcr.status<>?2 and fcr.credit_status in (?1) and fcr.cr_prod_id"
			+ " and fpt.cr_prod_type = ?3", nativeQuery = true)
	List<CreditRecordEntrity> findByCreditStatusAndStatusNot(
			List<String> statusList, int delete,String crProdType);

	/**
	 * 查询根据金融id查询相对应的融资信息
	 * 
	 * @param long1
	 * @date 2017年12月15日 @user hsg
	 */
	@Query(value = "select * from finance_cr_record fcr"
			+ " where fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ " and (?4 is null or fcr.credit_status=?4)"
			+ " and fcr.participator_user_id in (?5)", nativeQuery = true)
	List<CreditRecordEntrity> findConfirmFinance(int delete, Long purId,
			Long supId, String creditStatus, List<Long> supUserIdList);

	/**
	 * 根据企业id获取融资的计划付款id
	 */
	@Query(value = "select fcr.payment_id"
			+ " from finance_cr_record fcr"
			+ " where fcr.status<>?1"
			+ " and (?2 is null or fcr.originator_enterprise_id=?2)"
			+ " and fcr.credit_status not in ('WAIT_CONFIRM_CANCEL','WAIT_SUBMIT','WAIT_CONFIRM')", nativeQuery = true)
	List<Long> findPlayAmountId(int delete, Long enterpriseId);

	// 根据发票代码和发票号查询记录
	CreditRecordEntrity findByStatusNotAndInvoiceNoCode(int delete,
			String bill_num);

	// 根据发票号和发票代码查询融资记录
	CreditRecordEntrity findByInvoiceNoCodeAndCreditStatusNotAndStatusNot(
			String invoiceNo, String creditStatus, int delete);

	/**
	 * 根据状态查询对应的红标签数据
	 * 
	 * @param delete
	 * @param unusualNormalList 
	 * @param object
	 * @param participator
	 * @param object2
	 * @param object3
	 * @param userIdList
	 * @return
	 */
	@Query(value = "select count(1)"
			+ " from (select fcr.id from finance_cr_record fcr"
			+ " left join tb_xa_payment_plan pp on fcr.payment_id = pp.id"
			+ " where pp.status <> ?1 and fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ " and fcr.credit_status in(?4)"
			+ " and (fcr.originator_user_id in (?5) or"
			+ " fcr.participator_user_id in (?6))"
			+ " and fcr.credit_unusual_status in (?7)"
			+ " group by fcr.id) t", nativeQuery = true)
	Integer findLabelCount(int delete, Long purId, Long supId,
			List<String> statuList, List<Long> purIdList, List<Long> supIdList, List<String> unusualNormalList);
	/**
	 * 根据状态查询对应的红标签数据(待支付)
	 * 
	 * @param delete
	 * @param unusualNormalList 
	 * @param object
	 * @param participator
	 * @param object2
	 * @param object3
	 * @param userIdList
	 * @return
	 */
	@Query(value = "select count(1)"
			+ " from (select fcr.id from finance_cr_record fcr"
			+ " left join tb_xa_payment_plan pp on fcr.payment_id = pp.id"
			+ " left join finance_pay_order_item fpoi on fpoi.order_no = fcr.record_no and fpoi.pay_status  in (?8) and fpoi.trade_status = 'NORMAL'"
			+ " where pp.status <> ?1 and fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ " and fcr.credit_status in(?4)"
			+ " and (fcr.originator_user_id in (?5) or"
			+ " fcr.participator_user_id in (?6))"
			+ " and fcr.credit_unusual_status in (?7)"
			+ " and fpoi.id is null"
			+ " group by fcr.id) t", nativeQuery = true)
	Integer findWaitPayCount(int delete, Long purId, Long supId,
			List<String> statuList, List<Long> purIdList, List<Long> supIdList, List<String> unusualNormalList,List<String> orderStatusList);

	// 根融资编号查询融资记录
	CreditRecordEntrity findByStatusNotAndRecordNo(int delete, String orderId);

	// 根融资编号和融资状态集合查询融资记录
	@Query(value = "select * from finance_cr_record fcr"
			+ " where fcr.status<>?1 and fcr.record_no = ?2"
			+ " and fcr.credit_status in (?3)"
			, nativeQuery = true)
	List<CreditRecordEntrity> findByStatusListAndRecordNo(int delete,
			String orderId, List<String> statusList);

	/**
	 * 
	 * @param delete
	 * @param orderNo
	 * @param creditStatus
	 * @return
	 */
	CreditRecordEntrity findByStatusNotAndRecordNoAndCreditStatus(int delete,
			String orderNo, String creditStatus);

	/**
	 * 融资提交到银行改为待签合同
	 * 
	 * @param delete
	 * @param tid
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set credit_status = 'SUBSTITUTE_CONTRACT' where status <> ?1 and id = ?2", nativeQuery = true)
	Integer updateContract(int delete, Long tid);

	/**
	 * 修改融资状态改为线下支付待确认
	 * 
	 * @param delete
	 * @param delete
	 * @param tid
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set credit_status = 'WAIT_PAY' where status <> ?1 and record_no = ?2 and credit_status ='WAIT_PAY'", nativeQuery = true)
	Integer updatePayLine(int delete, String orderId);

	/**
	 * 根据条件查询导出的数据
	 * 
	 * @param delete
	 * @param originator
	 * @param participator
	 * @param statusList
	 * @param searchParm
	 * @param supEnteriseName
	 * @param purEnteriseName
	 * @param startTime
	 * @param loanEndDate
	 * @param object
	 * @param object2
	 * @param purUserList
	 * @param supUserList
	 * @return
	 */
	@Query(value = "select  fcr.contract_num,fcr.payment_num,fcr.record_no,fpt.cr_prod_bank_name,"
			+ " fecps.enterprise_name pur_enterprise_name,fecp.enterprise_name sup_enterprise_name,"
			+ " fcr.credit_amount,fcr.credit_get_amount,fcr.loan_date,fcr.repayment_date,"
			+ " fcr.credit_status,fcr.id,fcr.pur_repayment_amount,fcr.sup_repayment_amount,fcr.payment_id,"
			+ " fcr.draw_amount,fcr.repayment_amount,fcr.draw_rate,fcr.reject_reason,fcr.loan_date_ccb"
			+ " from finance_cr_record fcr "
			+ " left join finance_ent_account fecps on fecps.id=fcr.originator"
			+ " left join finance_ent_account fecp on fecp.id=fcr.participator"
			+ " left join finance_product_type fpt on fpt.id=fcr.cr_prod_id "
			+ " left join tb_xa_payment_plan pp on fcr.payment_id = pp.id"
			+ " where pp.status <> 3 and fcr.status<>?1"
			+ " and (?2 is null or fcr.originator=?2)"
			+ " and (?3 is null or fcr.participator=?3)"
			+ " and (?5 is null or fcr.contract_num like concat('%',?5,'%') or  fcr.payment_num like concat('%',?5,'%')"
			+ " or fecp.enterprise_name like concat('%',?5,'%') or fecps.enterprise_name like concat('%',?5,'%') or"
			+ " fcr.record_no like concat('%',?5,'%') or fpt.cr_prod_bank_name like concat('%',?5,'%') or fcr.payment_num like concat('%',?5,'%'))"
			+ " and (?6 is null or fecp.enterprise_name like concat('%',?6,'%'))"
			+ " and (?7 is null or fecps.enterprise_name like concat('%',?7,'%'))"
			+ " and (?8 is null or fcr.loan_date>=?8)"
			+ " and (?9 is null or fcr.loan_date<=?9)"
			+ " and (?10 is null or fcr.transaction_time>=?10)"
			+ " and (?11 is null or fcr.transaction_time<=?11)"
			+ " and (fcr.originator_user_id in (?12) or"
			+ " fcr.participator_user_id in (?13))"
			+ "	and fcr.credit_status in (?4)", nativeQuery = true)
	List<Object[]> findCreditRecordListForExcel(int delete, Long originator,
			Long participator, List<String> statusList, String searchParm,
			String supEnteriseName, String purEnteriseName, String startTime,
			Date loanEndDate, Object object, Object object2,
			List<Long> purUserList, List<Long> supUserList);

	/**
	 * 更新融资异常状态
	 * 
	 * @param delete
	 * @param tid
	 * @param string
	 * @param fail_reason
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set credit_unusual_status = ?3,reject_reason = ?4,credit_status = ?5 where status <> ?1 and id = ?2", nativeQuery = true)
	Integer updateUnusualStatus(int delete, Long tid,
			String creditUnusualStatus, String fail_reason,String creditStatus);
	/**
	 * 工商银行合同签订更新融资状态
	 * 
	 * @param delete
	 * @param tid
	 * @param string
	 * @param fail_reason
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set credit_unusual_status = ?2,reject_reason = ?3,credit_status = ?4 where id = ?1 and credit_status = 'SUBSTITUTE_CONTRACT'", nativeQuery = true)
	Integer updateBySignContract( Long tid,
			String creditUnusualStatus, String fail_reason,String creditStatus);

	CreditRecordEntrity findByPaymentIdAndStatusNot(Long paymentId, int delete);

	@Query(value = "select * from finance_cr_record where record_no = ?1",nativeQuery = true)
	CreditRecordEntrity findByRecordNo(String orderNo);

	/**
	 * 更新融资异常状态和发票信息
	 * @version 2018年7月26日
	 * @param creditUnusualStatus
	 * @param rejectReason
	 * @param string
	 * @param tid
	 * @param invoice_no_code
	 * @param TransactionTime 
	 * @param appNum 
	 * @param bankId 
	 * @param contNum 
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set credit_unusual_status = ?1,reject_reason = ?2,credit_status = ?3,invoice_tid = ?4,invoice_no_code =?5,transaction_time=?7,cr_prod_id = ?8,app_num = ?9,contract_num_ccb =?10 where status <> 3 and id = ?6", nativeQuery = true)
	Integer updateRecordInvoice(String creditUnusualStatus,
			String rejectReason, String credit_status, Long invoiceTid, String invoice_no_code,Long tid, Date TransactionTime, Long bankId, String appNum, String contNum);


	/**
	 * 合同签订更新方法
	 * @version 2018年7月26日
	 * @param delete
	 * @param tid
	 * @param string
	 * @param object
	 * @param creditStatus
	 * @param ar_due_date
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set credit_unusual_status = ?2,reject_reason = ?3,credit_status = ?4,loan_date_ccb = ?5 where id = ?1", nativeQuery = true)
	Integer updateBySignContract( Long tid, String credit_unusual_status,
			String reject_reason, String creditStatus, Date ar_due_date);


	CreditRecordEntrity findByStatusNotAndPaymentId(int delete, Long paymentId);

	

	/**
	 * 变更融资银行
	 * @version 2018年8月7日
	 * @param payId
	 * @param tid
	 * @param bankId
	 * @param appNum 
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set cr_prod_id = ?3 where 1=1"
			+ " and (?1 is null or payment_id = ?1)"
			+ " and (?2 is null or id = ?2)", nativeQuery = true)
	Integer updateBankProd(Long payId, Long tid, Long bankId);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set qryf_seqno = ?2 "
			+ " where id = ?1 and credit_status in ('SUMBIT_TO_BANK','WAIT_PAY')", nativeQuery = true)
	Integer updateFSeqno(Long tid, String fSeqno);

	
	/**
	 * 查询工行融资接口
	 * @version 2018年8月17日
	 * @param creditStatusList
	 * @param delete
	 * @return
	 */
	@Query(value="select fcr.* from finance_cr_record fcr"
			+ " left join finance_product_type fpt on fcr.cr_prod_id = fpt.id"
			+ " where fcr.credit_status in (?1) and fcr.status <> 3 and fcr.qryf_seqno is not null"
			+ " and fpt.cr_prod_type = 'ICBC_R_E_J'",nativeQuery=true)
	List<CreditRecordEntrity> findByCreditStatusAndStatusNotAndSeqNo(List<String> creditStatusList);
	/**
	 * 获取工行的最大最小计划付款日期
	 * @version 2018年8月17日
	 * @param purId 
	 * @param supId 
	 * @param creditStatusList
	 * @param delete
	 * @return
	 */
	@Query(value="select max(f.loan_date) maxTime,MIN(f.loan_date) minTime from finance_cr_record f left join finance_product_type "
			+ " p on  f.cr_prod_id = p.id where p.cr_prod_type = 'ICBC_R_E_J' and f.credit_status in (?1)"
			+ " and f.originator = ?2",nativeQuery=true)
	List<Object[]> findMaxMinTime(List<String> statusList, Long purId);


	/**
	 * g根据发票号代码查询融资记录
	 * @version 2018年8月20日
	 * @param invoiceNoCode 
	 * @return
	 */
	CreditRecordEntrity findByInvoiceNoCode(String invoiceNoCode);


	/**
	 * 工行还款信息更新
	 * @version 2018年8月20日
	 * @param tid
	 * @param creditAmount
	 * @param drawAmount
	 * @param drawRate
	 * @param purRepaymentAmount
	 * @param creditStatus
	 * @param loanDateCcb
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set credit_amount = ?2,draw_amount = ?3,draw_rate=?4,pur_repayment_amount=?5,credit_status = ?6,loan_date_ccb = ?7 where id = ?1", nativeQuery = true)
	Integer updatePaymentInfo(Long tid, BigDecimal creditAmount, BigDecimal drawAmount, BigDecimal drawRate, BigDecimal purRepaymentAmount, String creditStatus, Date loanDateCcb);

	
	/**
	 * 更新融资单里的报名编号
	 * @version 2018年9月11日
	 * @param tid
	 * @param appNum
	 * @param contractNum
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_cr_record set app_num = ?2, contract_num_ccb = ?3 where id = ?1", nativeQuery = true)
	void updateAppNum(Long tid, String appNum, String contractNum);


	@Query(value="select * from finance_cr_record where invoice_tid = ?1",nativeQuery=true)
	List<CreditRecordEntrity> findByInvoiceTid(Long invoiceTid);


	

	



	



	

}
